set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=100;
set hive.exec.max.dynamic.partitions.pernode=100;
with  adjudication_money as (  --金额取值:优先取申诉金额，如果存在arbitration_id，type相同取优先取create_time 最新
    select arbitration_id              --仲裁申报单id
         , responsibility_network_code --裁定网点编号
         , total--裁定金额
         , handling_fee
    from (
             select arbitration_id              --仲裁申报单id
                  , responsibility_network_code --裁定网点编号
                  , total--裁定金额
                  , handling_fee
                  , row_number() over (partition by arbitration_id, responsibility_network_code order by type desc ,create_time desc,id desc ) as rn
             from jms_dwd.dwd_adjudication_base_dt
             where dt <= '{{ execution_date | cst_ds }}'
               and dt >= date_add('{{ execution_date | cst_ds }}', -120)
               and is_delete != 0
               and arbitration_id is not null
               and responsibility_network_code is not null
         ) a
    where rn = 1
),
adjudication_opinion as ( --申诉裁定意见
     select arbitration_id              --仲裁申报单id
          , responsibility_network_code --裁定网点编号
          , max(if(type=1, adjudicate_opinion,null)) as award_opinion   -- 裁定意见--两行合成一行（一行有值一行为null）所以用了max
          , max(if(type=2, adjudicate_opinion,null)) as appeal_opinion  --申诉意见
     from (
              select arbitration_id              --仲裁申报单id
                   , responsibility_network_code --裁定网点编号
                   , type
                   , adjudicate_opinion
                   , row_number() over (partition by arbitration_id, responsibility_network_code,type order by create_time desc,id desc ) as rn
              from jms_dwd.dwd_adjudication_base_dt
              where dt <= '{{ execution_date | cst_ds }}'
                and dt >= date_add('{{ execution_date | cst_ds }}', -120)
                and is_delete != 0
          and arbitration_id is not null
          and responsibility_network_code is not null
          ) a
     where rn = 1
     group by arbitration_id,responsibility_network_code
),
  detail_base as (
    select
        '仲裁' as data_source --仲裁申报表
         ,2 as source_type  --2表示仲裁1线上理赔
         ,if(t.status=10,'遗失','疑似遗失') as loss_type --遗失类型
         ,t.waybill_no --运单号
         ,t.code    --理赔编号
         ,if(status=10,t.closing_time,t.create_time) as date_time  --提交时间
         ,null as submit_status --提交状态
         ,null as is_carrier --是否承运商
         ,null as source  --来源
         ,t.first_resp_network_id as first_duty_network_id     -- 责任网点1id
         ,if(trim(t.first_resp_network_code) = '',null,first_resp_network_code) as first_duty_network_code -- 责任网点1code
         ,t.first_resp_network_name as first_duty_network_name -- 责任网点1name
         ,t.first_resp_parent_id as first_duty_proxy_id      -- 责任网点1-代理区id
         ,t.first_resp_parent_code as first_duty_proxy_code  --责任网点1-代理区代码
         ,t.first_resp_parent_name as first_duty_proxy_name  --责任网点1-代理区名称
         ,null as first_duty_region_id   --责任网点1-所属大区id
         ,net1.manage_code as first_duty_region_code --责任网点1-所属大区代码
         ,net1.manage_name as first_duty_region_name --责任网点1-所属大区名称
         ,nvl(t.goods_value,0) as first_duty_pay_amount  --责任网点1-赔付金额
         ,net1.network_type as first_duty_network_type --责任网点1-网点类型
         ,net1.is_enable as first_is_enable --责任网点1-网点是否启用
         ,net1.is_suspend as first_is_suspende --责任网点1-网点是否暂停
         ,t.second_resp_network_id as second_duty_network_id -- 责任网点2-网点id
         ,if( trim(t.second_resp_network_code) = '',null, t.second_resp_network_code) as second_duty_network_code --责任网点2-网点code
         ,t.second_resp_network_name as second_duty_network_name --责任网点2-网点name
         ,t.second_resp_parent_id as second_duty_proxy_id    -- 责任网点2-代理区id
         ,t.second_resp_parent_code as second_duty_proxy_code  --责任网点2-代理区编码
         ,t.second_resp_parent_name as second_duty_proxy_name  -- 责任网点2-代理区名称
         ,null as second_duty_region_id   -- 责任网点2-所属大区id
         ,net2.manage_code as second_duty_region_code -- 责任网点2-所属大区编码
         ,net2.manage_name as second_duty_region_name -- 责任网点2-所属大区名称
         ,null as second_duty_pay_amount --责任网点2-赔付金额
         ,net2.network_type as second_duty_network_type  --责任网点2-网点类型
         ,net2.is_enable as second_is_enable --责任网点2-网点类型 是否启用
         ,net2.is_suspend as second_is_suspende --责任网点2-网点类型 是否暂停
         ,t.third_resp_network_id as third_duty_network_id      --责任网点3-网点id
         ,if( trim(t.third_resp_network_code) = '',null, t.third_resp_network_code)  as third_duty_network_code  --责任网点3-网点code
         ,t.third_resp_network_name as third_duty_network_name  --责任网点3-网点name
         ,t.third_resp_parent_id as third_duty_proxy_id    --责任网点3-代理区id
         ,t.third_resp_parent_code as third_duty_proxy_code  --责任网点3-代理区编码
         ,t.third_resp_parent_name as third_duty_proxy_name  --责任网点3-代理区名称
         ,null as third_duty_region_id   --责任网点3-所属大区id
         ,net3.manage_code as third_duty_region_code --责任网点3-所属大区编码
         ,net3.manage_name as third_duty_region_name --责任网点3-所属大区名称
         ,null as third_duty_pay_amount             --责任网点3-赔付金额
         ,net3.network_type as third_duty_network_type --责任网点3-网点类型
         ,net3.is_enable as third_is_enable --责任网点3-网点是否启用
         ,net3.is_suspend as third_is_suspende --责任网点3-网点是否暂停
         ,t.four_resp_network_id as four_duty_network_id      --责任网点4-网点id
         ,if(trim(t.four_resp_network_code) = '',null, t.four_resp_network_code) as four_duty_network_code  --责任网点4-网点code
         ,t.four_resp_network_name as four_duty_network_name  --责任网点4-网点name
         ,t.four_resp_parent_id as four_duty_proxy_id    -- 责任网点4-代理区id
         ,t.four_resp_parent_code as four_duty_proxy_code  -- 责任网点4-代理区编码
         ,t.four_resp_parent_name as four_duty_proxy_name  -- 责任网点4-代理区名称
         ,null as four_duty_region_id                -- 责任网点4-所属大区id
         ,net4.manage_code as four_duty_region_code -- 责任网点4-所属大区编码
         ,net4.manage_name as four_duty_region_name -- 责任网点4-所属大区名称
         ,null as four_duty_pay_amount  --责任网点4-赔付金额
         ,net4.network_type as four_duty_network_type --责任网点5-网点类型
         ,net4.is_enable as four_is_enable       --责任网点4-网点是否启用
         ,net4.is_suspend as four_is_suspende     --责任网点4-网点是否暂停
         ,t.five_resp_network_id as five_duty_network_id      --责任网点5-网点id
         ,if(trim(t.five_resp_network_code) = '',null, t.five_resp_network_code) as five_duty_network_code  --责任网点5-网点code
         ,t.five_resp_network_name as five_duty_network_name  --责任网点5-网点name
         ,t.five_resp_parent_id as five_duty_proxy_id      --责任网点5-代理区id
         ,t.five_resp_parent_code as five_duty_proxy_code  --责任网点5-代理区编码
         ,t.five_resp_parent_name as five_duty_proxy_name  --责任网点5-代理区名称
         ,null as five_duty_region_id            --责任网点5-所属大区id
         ,net5.manage_code five_duty_region_code --责任网点5-所属大区编码
         ,net5.manage_name five_duty_region_name --责任网点5-所属大区名称
         ,null as five_duty_pay_amount           --责任网点5-赔付金额
         ,net5.network_type as five_duty_network_type --责任网点5-网点类型
         ,net5.is_enable as five_is_enable       --责任网点5-网点是否启用
         ,net5.is_suspend as five_is_suspende    --责任网点5-网点是否暂停
         ,case when t.five_resp_network_code is not null  and trim(t.five_resp_network_code) !=''  then 5
               when t.four_resp_network_code is not null  and trim(t.four_resp_network_code) !=''  then 4
               when t.third_resp_network_code is not null and trim(t.third_resp_network_code) !='' then 3
               when t.second_resp_parent_code is not null and trim(t.second_resp_parent_code) !='' then 2
               when t.first_resp_network_code is not null and trim(t.first_resp_network_code) !='' then 1
               else 0
        end as network_count
         ,t.create_network_id
         ,t.create_network_code
         ,t.create_network_name
         ,t.create_parent_id
         ,t.create_parent_code
         ,t.create_parent_name
         ,null as create_region_id--申报网点大区id
         ,net6.manage_code as create_region_code--申报网点大区code
         ,net6.manage_name as create_region_name--申报网点大区name
         ,net6.network_type as create_network_type --申报网点类型
         ,net6.is_enable as create_is_enable --责任网点3-网点类型 是否启用
         ,net6.is_suspend as create_is_suspende --责任网点3-网点类型 是否暂停
         ,t.first_type_id --一级异常类型id
         ,t.first_type_code --一级异常类型code
         ,t.first_type --一级异常类型名称(冗余)
         ,t.second_type_id--二级异常类型id
         ,t.second_type_code --二级异常类型code
         ,t.second_type --二级异常类型名称(冗余)
         ,t.create_time  --仲裁申报时间
         ,t.delay_day  --延误天数
         ---,t.status as exception_desc --异常说明
         ,t.exception_desc
         ,t.arrival_time  --到件时间
         ,t.collect_time  --揽收时间
         ,t.handling_fee_total as handling_fee   --手续费需求是handling_fee实际取的是handling_fee_total
         ,t.is_public_distribution_network --是否共配网点
         ,t.station_name --门店名称
         ,t.business_name  --品牌名称
         ,nvl(net1.virt_code,net1.financial_center_code) as  first_virt_code
         ,nvl(net1.virt_name,net1.financial_center_desc) as  first_virt_name
         ,nvl(net2.virt_code,net2.financial_center_code) as  second_virt_code
         ,nvl(net2.virt_name,net2.financial_center_desc) as  second_virt_name
         ,nvl(net3.virt_code,net3.financial_center_code) as  third_virt_code
         ,nvl(net3.virt_name,net3.financial_center_desc) as  third_virt_name
         ,nvl(net4.virt_code,net4.financial_center_code) as  four_virt_code
         ,nvl(net4.virt_name,net4.financial_center_desc) as  four_virt_name
         ,nvl(net5.virt_code,net5.financial_center_code) as  five_virt_code
         ,nvl(net5.virt_name,net5.financial_center_desc) as  five_virt_name
         ,nvl(net1.virt_id,net1.financial_center_id) as  first_virt_id
         ,nvl(net2.virt_id,net2.financial_center_id) as  second_virt_id
         ,nvl(net3.virt_id,net3.financial_center_id) as  third_virt_id
         ,nvl(net4.virt_id,net4.financial_center_id) as  four_virt_id
         ,nvl(net5.virt_id,net5.financial_center_id) as  five_virt_id
         ,net1.provider_id   as first_provider_id
         ,net1.provider_desc as first_provider_desc --省份
         ,net1.city_id       as first_city_id      --城市ID
         ,net1.city_desc     as first_city_desc    --城市
         ,net1.area_id       as first_area_id      --区/县id
         ,net1.area_desc     as first_area_desc    --区/县
         ,net1.zone_id       as first_district_id
         ,net1.zone_code     as first_district_code
         ,net1.zone_name     as first_district_name
         ----
         ,net2.provider_id   as second_provider_id
         ,net2.provider_desc as second_provider_desc --省份
         ,net2.city_id       as second_city_id      --城市ID
         ,net2.city_desc     as second_city_desc    --城市
         ,net2.area_id       as second_area_id      --区/县id
         ,net2.area_desc     as second_area_desc    --区/县
         ,net2.zone_id       as second_district_id
         ,net2.zone_code     as second_district_code
         ,net2.zone_name     as second_district_name
         ----
         ,net3.provider_id   as third_provider_id
         ,net3.provider_desc as third_provider_desc --省份
         ,net3.city_id       as third_city_id      --城市ID
         ,net3.city_desc     as third_city_desc    --城市
         ,net3.area_id       as third_area_id      --区/县id
         ,net3.area_desc     as third_area_desc    --区/县
         ,net3.zone_id       as third_district_id
         ,net3.zone_code     as third_district_code
         ,net3.zone_name     as third_district_name
         ---
         ,net4.provider_id   as four_provider_id
         ,net4.provider_desc as four_provider_desc --省份
         ,net4.city_id       as four_city_id      --城市ID
         ,net4.city_desc     as four_city_desc    --城市
         ,net4.area_id       as four_area_id      --区/县id
         ,net4.area_desc     as four_area_desc    --区/县
         ,net4.zone_id       as four_district_id
         ,net4.zone_code     as four_district_code
         ,net4.zone_name     as four_district_name
         ---
         ,net5.provider_id   as five_provider_id
         ,net5.provider_desc as five_provider_desc --省份
         ,net5.city_id       as five_city_id      --城市ID
         ,net5.city_desc     as five_city_desc    --城市
         ,net5.area_id       as five_area_id      --区/县id
         ,net5.area_desc     as five_area_desc    --区/县
         ,net5.zone_id       as five_district_id
         ,net5.zone_code     as five_district_code
         ,net5.zone_name     as five_district_name
         ,t.handling_fee_total   --手续费
         ,t.award_amount_total   --裁定金额
         , null as amount
         ,is_closed_last
         ,adj1.total as award_amount_1
         ,adj1.handling_fee as handling_fee_1
         ,adj2.total as award_amount_2
         ,adj2.handling_fee as handling_fee_2
         ,adj3.total as award_amount_3
         ,adj3.handling_fee as handling_fee_3
         ,adj4.total as award_amount_4
         ,adj4.handling_fee as handling_fee_4
         ,adj5.total as award_amount_5
         ,adj5.handling_fee as handling_fee_5
         ,t.id
         ,net1.leaf_area_id        as area_id_1
         ,net1.area_code as area_code_1  --网管片区_责任网点1
         ,net1.area_name as area_name_1  --网管片区_责任网点1
         ,net2.leaf_area_id       as  area_id_2
         ,net2.area_code as area_code_2  --网管片区_责任网点2
         ,net2.area_name as area_name_2  --网管片区_责任网点2
         ,net3.leaf_area_id       as  area_id_3
         ,net3.area_code as area_code_3  --网管片区_责任网点3
         ,net3.area_name as area_name_3  --网管片区_责任网点3
         ,net4.leaf_area_id       as  area_id_4
         ,net4.area_code as area_code_4  --网管片区_责任网点4
         ,net4.area_name as area_name_4  --网管片区_责任网点4
         ,net5.leaf_area_id       as  area_id_5
         ,net5.area_code as area_code_5  --网管片区_责任网点5
         ,net5.area_name as area_name_5  --网管片区_责任网点5
         ,nvl(t.goods_value,0) as goods_value --物品价值
         ,arbitration_award_time         --仲裁裁定时间
         ,closing_time                   --结案时间
         ,is_package_inner_lost as is_package_inner       --是否是包内件
         ,coalesce(ado1.award_opinion,ado2.award_opinion,ado3.award_opinion,ado4.award_opinion,ado5.award_opinion) as award_opinion --仲裁裁定处理意见
         ,coalesce(ado1.appeal_opinion,ado2.appeal_opinion,ado3.appeal_opinion,ado4.appeal_opinion,ado5.appeal_opinion) as appeal_opinion --申诉裁定处理意见
         ,responsibility_reply_opinion   --责任方回复信息
         ,date(if(t.status=10,t.closing_time,t.create_time)) as dt  --shijain
from jms_dwd.dwd_arbitration_base_dt t
    left join jms_dim.dim_network_whole_massage net1 on net1.code = t.first_resp_network_code
    left join jms_dim.dim_network_whole_massage net2 on net2.code = t.second_resp_network_code
    left join jms_dim.dim_network_whole_massage net3 on net3.code = t.third_resp_network_code
    left join jms_dim.dim_network_whole_massage net4 on net4.code = t.four_resp_network_code
    left join jms_dim.dim_network_whole_massage net5 on net5.code = t.five_resp_network_code
    left join jms_dim.dim_network_whole_massage net6 on net6.code = t.create_network_code
    left join adjudication_money adj1 on adj1.arbitration_id=t.id and  adj1.responsibility_network_code=t.first_resp_network_code
    left join adjudication_money adj2 on adj2.arbitration_id=t.id and  adj2.responsibility_network_code=t.second_resp_network_code
    left join adjudication_money adj3 on adj3.arbitration_id=t.id and  adj3.responsibility_network_code=t.third_resp_network_code
    left join adjudication_money adj4 on adj4.arbitration_id=t.id and  adj4.responsibility_network_code=t.four_resp_network_code
    left join adjudication_money adj5 on adj5.arbitration_id=t.id and  adj5.responsibility_network_code=t.five_resp_network_code
    left join adjudication_opinion ado1 on ado1.arbitration_id=t.id and  ado1.responsibility_network_code=t.first_resp_network_code
    left join adjudication_opinion ado2 on ado2.arbitration_id=t.id and  ado2.responsibility_network_code=t.second_resp_network_code
    left join adjudication_opinion ado3 on ado3.arbitration_id=t.id and  ado3.responsibility_network_code=t.third_resp_network_code
    left join adjudication_opinion ado4 on ado4.arbitration_id=t.id and  ado4.responsibility_network_code=t.four_resp_network_code
    left join adjudication_opinion ado5 on ado5.arbitration_id=t.id and  ado5.responsibility_network_code=t.five_resp_network_code
where t.dt between date_add(trunc('{{ execution_date | cst_ds }}','MM'),-120) and '{{ execution_date | cst_ds }}'
  and date(if(t.status=10,t.closing_time,t.create_time)) between trunc('{{ execution_date | cst_ds }}','MM') and '{{ execution_date | cst_ds }}'
  and ((t.first_type_code ='03' and t.second_type_code !='0324')
   or (t.first_type_code ='04' and t.second_type_code ='0403')
   or (t.first_type_code ='05' and t.second_type_code ='0503')
   or (t.first_type_code ='01' and t.second_type_code ='0112')
   or (t.first_type_code ='09' and t.second_type_code ='0901')
    )

union all

SELECT
    '线上理赔' as data_source --线上理赔
     ,1 as source_type  --2表示仲裁1线上理赔
     ,'遗失' as loss_type --遗失类型
     ,t.waybill_no --运单号
     ,t.claim_no as code   --理赔编号
     ,t.submit_time  as date_time --提交时间
     ,t.submit_status --提交状态
     ,t.is_carrier --是否承运商
     ,t.source  --来源
     ,t.first_duty_network_id  -- 责任网点1id
     ,if(trim(t.first_duty_network_code) = '',null, t.first_duty_network_code) as first_duty_network_code
     ,t.first_duty_network_name
     ,t.first_duty_proxy_id    -- '责任网点1-代理区id'
     ,t.first_duty_proxy_code  --'责任网点1-代理区代码'
     ,t.first_duty_proxy_name  -- '责任网点1-代理区名称'
     ,t.first_duty_region_id   -- '责任网点1-所属大区id'
     ,t.first_duty_region_code -- '责任网点1-所属大区代码'
     ,t.first_duty_region_name -- '责任网点1-所属大区名称'
     ,nvl(t.first_duty_pay_amount,0)  as first_duty_pay_amount --责任网点1-赔付金额
     ,net1.network_type as first_duty_network_type --责任网点1-网点类型
     ,net1.is_enable as first_is_enable --责任网点1-网点类型 是否启用
     ,net1.is_suspend as first_is_suspende --责任网点1-网点类型 是否暂停
     ,t.second_duty_network_id
     ,if( trim(t.second_duty_network_code) = '',null, t.second_duty_network_code) as second_duty_network_code
     ,t.second_duty_network_name
     ,t.second_duty_proxy_id    -- '责任网点2-代理区id'
     ,t.second_duty_proxy_code  -- '责任网点2-代理区编码'
     ,t.second_duty_proxy_name  -- '责任网点2-代理区名称'
     ,t.second_duty_region_id   -- '责任网点2-所属大区id'
     ,t.second_duty_region_code -- '责任网点2-所属大区编码'
     ,t.second_duty_region_name -- '责任网点2-所属大区名称'
     ,nvl(t.second_duty_pay_amount,0) as second_duty_pay_amount --责任网点2-赔付金额
     ,net2.network_type  as second_duty_network_type  --责任网点2-网点类型
     ,net2.is_enable as second_is_enable --责任网点2-网点类型 是否启用
     ,net2.is_suspend as second_is_suspende --责任网点2-网点类型
     ,t.third_duty_network_id
     ,if(trim(t.third_duty_network_code) = '',null, t.third_duty_network_code) as third_duty_network_code
     ,t.third_duty_network_name
     ,t.third_duty_proxy_id    --'责任网点3-代理区id'
     ,t.third_duty_proxy_code  --'责任网点3-代理区编码'
     ,t.third_duty_proxy_name  --'责任网点3-代理区名称'
     ,t.third_duty_region_id   --'责任网点3-所属大区id'
     ,t.third_duty_region_code --'责任网点3-所属大区编码'
     ,t.third_duty_region_name --'责任网点3-所属大区名称'
     ,nvl(t.third_duty_pay_amount,0) as  third_duty_pay_amount  --责任网点3-赔付金额
     ,net3.network_type as third_duty_network_type --责任网点3-网点类型
     ,net3.is_enable as third_is_enable --责任网点3-网点类型 是否启用
     ,net3.is_suspend as third_is_suspende --责任网点3-网点类型 是否暂停
     ,null as four_duty_network_id
     ,null as four_duty_network_code
     ,null as four_duty_network_name
     ,null as four_duty_proxy_id    -- '责任网点2-代理区id'
     ,null as four_duty_proxy_code  -- '责任网点2-代理区编码'
     ,null as four_duty_proxy_name  -- '责任网点2-代理区名称'
     ,null as four_duty_region_id   -- '责任网点2-所属大区id'
     ,null as four_duty_region_code -- '责任网点2-所属大区编码'
     ,null as four_duty_region_name -- '责任网点2-所属大区名称'
     ,null as four_duty_pay_amount --责任网点2-赔付金额
     ,null as four_duty_network_type --责任网点5-网点类型
     ,null as four_is_enable --责任网点3-网点类型 是否启用
     ,null as four_is_suspende --责任网点3-网点类型 是否暂停
     ,null as five_duty_network_id
     ,null as five_duty_network_code
     ,null as five_duty_network_name
     ,null as five_duty_proxy_id    --'责任网点3-代理区id'
     ,null as five_duty_proxy_code  --'责任网点3-代理区编码'
     ,null as five_duty_proxy_name  --'责任网点3-代理区名称'
     ,null as five_duty_region_id   --'责任网点3-所属大区id'
     ,null as five_duty_region_code --'责任网点3-所属大区编码'
     ,null as five_duty_region_name --'责任网点3-所属大区名称'
     ,null as five_duty_pay_amount  --责任网点3-赔付金额
     ,null as five_duty_network_type --责任网点5-网点类型
     ,null as five_is_enable --责任网点3-网点类型 是否启用
     ,null as five_is_suspende --责任网点3-网点类型 是否暂停
     ,t.network_count
     ,null as create_network_id
     ,null as create_network_code
     ,null as create_network_name
     ,null as create_parent_id
     ,null as create_parent_code
     ,null as create_parent_name
     ,null as create_region_id--申报网点大区id
     ,null as create_region_code--申报网点大区code
     ,null as create_region_name--申报网点大区name
     ,null as create_network_type --申报网点类型
     ,null as create_is_enable --责任网点3-网点类型 是否启用
     ,null as create_is_suspende --责任网点3-网点类型
     ,'000' as first_type_id
     ,'9999' as first_type_code --一级异常类型code
     ,'线上赔付遗失' as first_type --一级异常类型名称(冗余)
     ,'0000' as second_type_id--二级异常类型id
     ,'9999a' as second_type_code --二级异常类型code
     ,'线上赔付遗失' as second_type --二级异常类型名称(冗余)
     ,null as create_time --仲裁申报时间
     ,null as delay_day  --延误天数
     ,null as exception_desc --异常说明
     ,null as arrival_time  --到件时间
     ,null as collect_time  --揽收时间
     ,null as handling_fee   --手续费
     ,null as is_public_distribution_network --是否共配网点
     ,null as station_name --门店名称
     ,null as business_name  --品牌名称
     ,nvl(net1.virt_code,net1.financial_center_code) as  first_virt_code
     ,nvl(net1.virt_name,net1.financial_center_desc) as  first_virt_name
     ,nvl(net2.virt_code,net2.financial_center_code) as  second_virt_code
     ,nvl(net2.virt_name,net2.financial_center_desc) as  second_virt_name
     ,nvl(net3.virt_code,net3.financial_center_code) as  third_virt_code
     ,nvl(net3.virt_name,net3.financial_center_desc) as  third_virt_name
     ,null as four_virt_code
     ,null as four_virt_name
     ,null as five_virt_code
     ,null as five_virt_name
     ,nvl(net1.virt_id,net1.financial_center_id) as  first_virt_id
     ,nvl(net2.virt_id,net2.financial_center_id) as  second_virt_id
     ,nvl(net3.virt_id,net3.financial_center_id) as  third_virt_id
     ,null as  four_virt_id
     ,null as  five_virt_id
     ,net1.provider_id   as first_provider_id
     ,net1.provider_desc as first_provider_desc --省份
     ,net1.city_id       as first_city_id      --城市ID
     ,net1.city_desc     as first_city_desc    --城市
     ,net1.area_id       as first_area_id      --区/县id
     ,net1.area_desc     as first_area_desc    --区/县
     ,net1.zone_id       as first_district_id
     ,net1.zone_code     as first_district_code
     ,net1.zone_name     as first_district_name
----
     ,net2.provider_id   as second_provider_id
     ,net2.provider_desc as second_provider_desc --省份
     ,net2.city_id       as second_city_id      --城市ID
     ,net2.city_desc     as second_city_desc    --城市
     ,net2.area_id       as second_area_id      --区/县id
     ,net2.area_desc     as second_area_desc    --区/县
     ,net2.zone_id       as second_district_id
     ,net2.zone_code     as second_district_code
     ,net2.zone_name     as second_district_name
----
     ,net3.provider_id   as third_provider_id
     ,net3.provider_desc as third_provider_desc --省份
     ,net3.city_id       as third_city_id      --城市ID
     ,net3.city_desc     as third_city_desc    --城市
     ,net3.area_id       as third_area_id      --区/县id
     ,net3.area_desc     as third_area_desc    --区/县
     ,net3.zone_id       as third_district_id
     ,net3.zone_code     as third_district_code
     ,net3.zone_name     as third_district_name
     ,null as four_provider_id
     ,null as four_provider_desc --省份
     ,null as four_city_id      --城市ID
     ,null as four_city_desc    --城市
     ,null as four_area_id      --区/县id
     ,null as four_area_desc    --区/县
     ,null as four_district_id
     ,null as four_district_code
     ,null as four_district_name
---
     ,null as five_provider_id
     ,null as five_provider_desc --省份
     ,null as five_city_id      --城市ID
     ,null as five_city_desc    --城市
     ,null as five_area_id      --区/县id
     ,null as five_area_desc    --区/县
     ,null as five_district_id
     ,null as five_district_code
     ,null as five_district_name
     ,null as handling_fee_total   --手续费
     ,null as award_amount_total   --裁定金额
     ,50 as amount
     ,null as is_closed_last
     ,null as award_amount_1
     ,null as handling_fee_1
     ,null as award_amount_2
     ,null as handling_fee_2
     ,null as award_amount_3
     ,null as handling_fee_3
     ,null as award_amount_4
     ,null as handling_fee_4
     ,null as award_amount_5
     ,null as handling_fee_5
     ,t.id
     ,net1.leaf_area_id  as  area_id_1
     ,net1.area_code as area_code_1  --网管片区_责任网点1
     ,net1.area_name as area_name_1  --网管片区_责任网点1
     ,net2.leaf_area_id  as  area_id_2
     ,net2.area_code as area_code_2  --网管片区_责任网点2
     ,net2.area_name as area_name_2  --网管片区_责任网点2
     ,net3.leaf_area_id  as  area_id_3
     ,net3.area_code as area_code_3  --网管片区_责任网点3
     ,net3.area_name as area_name_3  --网管片区_责任网点3
     ,null as area_id_4
     ,null as area_code_4  --网管片区_责任网点4
     ,null as area_name_4  --网管片区_责任网点4
     ,null as area_id_5
     ,null as area_code_5  --网管片区_责任网点5
     ,null as area_name_5  --网管片区_责任网点5
     ,0 as goods_value --物品价值
     ,null as arbitration_award_time         --仲裁裁定时间
     ,null as closing_time                   --结案时间
     ,null as is_package_inner       --是否是包内件
     ,null as award_opinion --仲裁裁定处理意见
     ,null as appeal_opinion --申诉裁定处理意见
     ,null as responsibility_reply_opinion   --责任方回复信息
     ,date(t.submit_time) as dt
FROM jms_dwd.dwd_sqs_claim_detail_base_dt t
    left join jms_dim.dim_network_whole_massage net1 on net1.code = t.first_duty_network_code
    left join jms_dim.dim_network_whole_massage net2 on net2.code = t.second_duty_network_code
    left join jms_dim.dim_network_whole_massage net3 on net3.code = t.third_duty_network_code

where dt between date_add(trunc('{{ execution_date | cst_ds }}','MM'),-1) and '{{ execution_date | cst_ds }}'
  and date(t.submit_time) between trunc('{{ execution_date | cst_ds }}','MM') and '{{ execution_date | cst_ds }}'
  and t.is_carrier=2 --是否承运商 2否
  and t.valid_status=1 --生效状态为是
  and t.submit_status=1 --提交状态 为是
  and t.problem_type =1 -- 1-遗失
  and t.source=1
    )


insert overwrite table  jms_dm.dm_sqs_loss_detail_dt
select
    case when source_type=2 and is_closed_last=1 then 2
         else
             row_number() over(partition by t1.waybill_no ,t1.loss_type order by t1.source_type desc,t1.date_time)
        end as is_filter
     ,t1.data_source
     ,t1.loss_type
     ,t1.waybill_no
     ,t1.code
     ,t1.date_time
     ,t1.submit_status
     ,t1.is_carrier
     ,t1.source
     ,t1.first_duty_network_id
     ,t1.first_duty_network_code
     ,t1.first_duty_network_name
     ,t1.first_duty_proxy_id
     ,t1.first_duty_proxy_code
     ,t1.first_duty_proxy_name
     ,t1.first_duty_region_id
     ,t1.first_duty_region_code
     ,t1.first_duty_region_name
     ,t1.first_duty_pay_amount
     ,t1.first_duty_network_type
     ,t1.first_is_enable
     ,t1.first_is_suspende
     ,t1.second_duty_network_id
     ,t1.second_duty_network_code
     ,t1.second_duty_network_name
     ,t1.second_duty_proxy_id
     ,t1.second_duty_proxy_code
     ,t1.second_duty_proxy_name
     ,t1.second_duty_region_id
     ,t1.second_duty_region_code
     ,t1.second_duty_region_name
     ,t1.second_duty_pay_amount
     ,t1.second_duty_network_type
     ,t1.second_is_enable
     ,t1.second_is_suspende
     ,t1.third_duty_network_id
     ,t1.third_duty_network_code
     ,t1.third_duty_network_name
     ,t1.third_duty_proxy_id
     ,t1.third_duty_proxy_code
     ,t1.third_duty_proxy_name
     ,t1.third_duty_region_id
     ,t1.third_duty_region_code
     ,t1.third_duty_region_name
     ,t1.third_duty_pay_amount
     ,t1.third_duty_network_type
     ,t1.third_is_enable
     ,t1.third_is_suspende
     ,t1.four_duty_network_id
     ,t1.four_duty_network_code
     ,t1.four_duty_network_name
     ,t1.four_duty_proxy_id
     ,t1.four_duty_proxy_code
     ,t1.four_duty_proxy_name
     ,t1.four_duty_region_id
     ,t1.four_duty_region_code
     ,t1.four_duty_region_name
     ,t1.four_duty_pay_amount
     ,t1.four_duty_network_type
     ,t1.four_is_enable
     ,t1.four_is_suspende
     ,t1.five_duty_network_id
     ,t1.five_duty_network_code
     ,t1.five_duty_network_name
     ,t1.five_duty_proxy_id
     ,t1.five_duty_proxy_code
     ,t1.five_duty_proxy_name
     ,t1.five_duty_region_id
     ,t1.five_duty_region_code
     ,t1.five_duty_region_name
     ,t1.five_duty_pay_amount
     ,t1.five_duty_network_type
     ,t1.five_is_enable
     ,t1.five_is_suspende
     ,t1.network_count
     ,t1.create_network_id
     ,t1.create_network_code
     ,t1.create_network_name
     ,t1.create_parent_id
     ,t1.create_parent_code
     ,t1.create_parent_name
     ,t1.create_region_id
     ,t1.create_region_code
     ,t1.create_region_name
     ,t1.create_network_type
     ,t1.create_is_enable
     ,t1.create_is_suspende
     ,t1.first_type_id
     ,t1.first_type_code
     ,t1.first_type
     ,t1.second_type_id
     ,t1.second_type_code
     ,t1.second_type
     ,t1.create_time
     ,t1.delay_day
     ,t1.exception_desc
     ,t1.arrival_time
     ,nvl(t1.collect_time,t2.collect_time) as collect_time
     ,t1.handling_fee
     ,t1.is_public_distribution_network
     ,t1.station_name
     ,t1.business_name
     ,t2.pick_network_code
     ,t2.pick_network_name
     ,t2.customer_code
     ,t2.customer_name
     ,t2.ordersource_code
     ,t2.ordersource_name
     ,t1.first_virt_code
     ,t1.first_virt_name
     ,t1.second_virt_code
     ,t1.second_virt_name
     ,t1.third_virt_code
     ,t1.third_virt_name
     ,t1.four_virt_code
     ,t1.four_virt_name
     ,t1.five_virt_code
     ,t1.five_virt_name
     ,t1.first_virt_id
     ,t1.second_virt_id
     ,t1.third_virt_id
     ,t1.four_virt_id
     ,t1.five_virt_id
     ,t1.first_provider_id
     ,t1.first_provider_desc --省份
     ,t1.first_city_id      --城市ID
     ,t1.first_city_desc    --城市
     ,t1.first_area_id      --区/县id
     ,t1.first_area_desc    --区/县
     ,t1.first_district_id
     ,t1.first_district_code
     ,t1.first_district_name
     ,t1.second_provider_id
     ,t1.second_provider_desc --省份
     ,t1.second_city_id      --城市ID
     ,t1.second_city_desc    --城市
     ,t1.second_area_id      --区/县id
     ,t1.second_area_desc    --区/县
     ,t1.second_district_id
     ,t1.second_district_code
     ,t1.second_district_name
     ,t1.third_provider_id
     ,t1.third_provider_desc --省份
     ,t1.third_city_id      --城市ID
     ,t1.third_city_desc    --城市
     ,t1.third_area_id      --区/县id
     ,t1.third_area_desc    --区/县
     ,t1.third_district_id
     ,t1.third_district_code
     ,t1.third_district_name
     ,t1.four_provider_id
     ,t1.four_provider_desc --省份
     ,t1.four_city_id      --城市ID
     ,t1.four_city_desc    --城市
     ,t1.four_area_id      --区/县id
     ,t1.four_area_desc    --区/县
     ,t1.four_district_id
     ,t1.four_district_code
     ,t1.four_district_name
     ,t1.five_provider_id
     ,t1.five_provider_desc --省份
     ,t1.five_city_id      --城市ID
     ,t1.five_city_desc    --城市
     ,t1.five_area_id      --区/县id
     ,t1.five_area_desc    --区/县
     ,t1.five_district_id
     ,t1.five_district_code
     ,t1.five_district_name
     ,handling_fee_total   --手续费
     ,award_amount_total   --裁定金额
     ,t2.dispatch_staff_code
     ,t2.dispatch_staff_name
     ,t1.source_type
     ,t1.is_closed_last
     ,t1.award_amount_1
     ,t1.handling_fee_1
     ,t1.award_amount_2
     ,t1.handling_fee_2
     ,t1.award_amount_3
     ,t1.handling_fee_3
     ,t1.award_amount_4
     ,t1.handling_fee_4
     ,t1.award_amount_5
     ,t1.handling_fee_5
     ,t1.id
     ,t1.area_id_1
     ,t1.area_code_1  --网管片区_责任网点1
     ,t1.area_name_1  --网管片区_责任网点1
     ,t1.area_id_2
     ,t1.area_code_2  --网管片区_责任网点2
     ,t1.area_name_2  --网管片区_责任网点2
     ,t1.area_id_3
     ,t1.area_code_3  --网管片区_责任网点3
     ,t1.area_name_3  --网管片区_责任网点3
     ,t1.area_id_4
     ,t1.area_code_4  --网管片区_责任网点4
     ,t1.area_name_4  --网管片区_责任网点4
     ,t1.area_id_5
     ,t1.area_code_5  --网管片区_责任网点5
     ,t1.area_name_5  --网管片区_责任网点5
     ,t1.goods_value
     ,t1.arbitration_award_time         --仲裁裁定时间
     ,t1.closing_time                   --结案时间
     ,t1.is_package_inner       --是否是包内件
     ,t1.award_opinion --仲裁裁定处理意见
     ,t1.appeal_opinion --申诉裁定处理意见
     ,t1.responsibility_reply_opinion   --责任方回复信息
     ,t1.dt
from detail_base t1
     left join
     (
         select waybill_no , collect_time,pick_network_code,pick_network_name,
                customer_code ,customer_name ,ordersource_code ,ordersource_name
                 ,dispatch_staff_code,dispatch_staff_name
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
         where dt between date_add('{{ execution_date | cst_ds }}',-120) and '{{ execution_date | cst_ds }}'
     )t2 on t1.waybill_no=t2.waybill_no
distribute by pmod(hash(rand()), 3)
;
insert overwrite table  jms_dm.dm_sqs_loss_network_detail_mid_dt
select
    date_time  --时间
     ,tt.waybill_no --运单号
     ,tt.loss_type --遗失类型
     ,tt.network_count --责任网点数
     ,tt.network_code --网点编码
     ,net.name as network_name --网点name
     ,net.manage_code  --大区code
     ,net.manage_name --大区name
     ,nvl(net.virt_code ,net.agent_code) as virt_code --虚拟代理区code
     ,nvl(net.virt_name ,net.agent_name) as virt_name --虚拟代理区name
     ,net.agent_code as financial_center_code --代理区code
     ,net.agent_name as financial_center_name --代理区名称
     ,first_type_id    --一级异常类型id
     ,first_type_code  --一级异常类型code
     ,first_type       --一级异常类型名称(冗余)
     ,second_type_id   --二级异常类型id
     ,second_type_code --二级异常类型code
     ,second_type      --二级异常类型名称(冗余)
     ,data_source      --数据来源
     ,net.network_type --网点类型
     ,case when source_type=2 and loss_type='遗失' and first_type_code='01'  and second_type_code='0112'   then award_amount --彻底延误=裁定金额
           when source_type=2 and loss_type='遗失' and first_type_code!='01'  and second_type_code!='0112'  and handling_fee<=0 then handling_fee
           when source_type=2 and loss_type='遗失' and first_type_code!='01'  and second_type_code!='0112'  and handling_fee>0 then 15+handling_fee
           when source_type=1 and loss_type='遗失'  then 50
      else 0
      end as network_amount --赔付金额
      ,source_type
      ,goods_value --物品价值
      ,date(date_time) as dt
from
(
    select
        waybill_no
        ,loss_type
        ,split(netd.network_info,':')[0] as network_code
        ,split(netd.network_info,':')[1] as award_amount
        ,split(netd.network_info,':')[2] as handling_fee
        ,network_count
        ,date_time
        ,first_type_id    --一级异常类型id
        ,first_type_code  --一级异常类型code
        ,first_type       --一级异常类型名称(冗余)
        ,second_type_id   --二级异常类型id
        ,second_type_code --二级异常类型code
        ,second_type  --二级异常类型名称(冗余)
        ,data_source
        ,source_type
        ,goods_value
    from
    (
    select
    waybill_no , loss_type,date_time
        ,first_type_id    --一级异常类型id
        ,first_type_code  --一级异常类型code
        ,first_type       --一级异常类型名称(冗余)
        ,second_type_id   --二级异常类型id
        ,second_type_code --二级异常类型code
        ,second_type  --二级异常类型名称(冗余)
        ,data_source
        ,ARRAY(
            concat(first_duty_network_code, ':',nvl(award_amount_1,0) , ':', nvl(handling_fee_1,0)),
            concat(second_duty_network_code,':',nvl(award_amount_2,0) , ':', nvl(handling_fee_2,0)),
            concat(third_duty_network_code, ':',nvl(award_amount_3,0) , ':', nvl(handling_fee_3,0)),
            concat(four_duty_network_code,  ':',nvl(award_amount_4,0) , ':', nvl(handling_fee_4,0)),
            concat(five_duty_network_code,  ':',nvl(award_amount_5,0) , ':', nvl(handling_fee_5,0))
        ) as duty_arr
        ,network_count --网点数
        ,source_type
        ,goods_value
    from  jms_dm.dm_sqs_loss_detail_dt
    where substr(dt,1,7) = substr('{{ execution_date | cst_ds }}',1,7) and is_filter =1
    )t LATERAL VIEW explode(duty_arr) netd as network_info
    where netd.network_info is not null
)tt left join jms_dim.dim_network_whole_massage net on tt.network_code=net.code
distribute by 1;